﻿using Log.Recorder.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;

namespace Log.Recorder
{
    /// <summary>
    /// 
    /// </summary>
    class DbWriter : IDisposable
    {
        private string _connectionString;
        private SQLiteConnection _conn;
        
        /// <summary>
        /// 活动时间
        /// </summary>
        public DateTime ActivityTime;
        public bool IsLogging = false;

        public SQLiteConnection Connection
        {
            get
            {
                if (_conn == null)
                {
                    _conn = new SQLiteConnection(_connectionString);
                    _conn.Open();
                }

                if (_conn.State == ConnectionState.Closed)
                {
                    _conn.Open();
                }
                else if (_conn.State == ConnectionState.Broken)
                {
                    _conn.Close();
                    _conn.Open();
                }

                return _conn;
            }
        }

        public DbWriter(string connectionString) {
            ActivityTime = DateTime.Now;
            _connectionString = connectionString;
            _conn = new SQLiteConnection(_connectionString);
            _conn.Open();
        }

        /// <summary>
        /// MD5 32位加密 加密后密文为小写
        /// </summary>
        /// <param name="str">待加密的字符串</param>
        /// <param name="len">密文字符串长度。16/32</param>
        /// <returns></returns>
        private static string Md5(string str, bool is16Bit = false)
        {
            System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create();
            byte[] md5Bytes = md5.ComputeHash(System.Text.Encoding.UTF8.GetBytes(str));
            string md5Str = is16Bit ? BitConverter.ToString(md5Bytes, 4, 8) : BitConverter.ToString(md5Bytes);
            return md5Str.Replace("-", "").ToLower();
        }

        /// <summary>
        /// 
        /// </summary>
        public int GetTableCount()
        {
            SQLiteCommand cmdDB = null;
            try
            {
                string sql = " SELECT count(*) num FROM sqlite_master WHERE type='table' AND name in ('Logs','LogsData') ";
                cmdDB = new SQLiteCommand(sql, Connection);
                cmdDB.CommandType = CommandType.Text;
                cmdDB.CommandTimeout = 60;//单位为秒
                return Convert.ToInt32(cmdDB.ExecuteScalar());
            }
            catch { return 0; }
            finally {
                if (cmdDB != null)
                {
                    cmdDB.Dispose();
                }
            }
        }

        #region 创建数据库

        /// <summary>
        /// 创建数据库
        /// </summary>
        public void CreateDatabase() {
            string sqlDB = @"
CREATE TABLE IF NOT EXISTS Logs (
    ID       BIGINT  NOT NULL
                           PRIMARY KEY,
    Time     DATETIME      NOT NULL
                           DEFAULT (datetime('now', 'localtime') ),
    Level    VARCHAR (150) NOT NULL,
    Type     VARCHAR (150) NULL,
    StackTrace  TEXT,
    Message  TEXT
);
CREATE TABLE IF NOT EXISTS LogsData (
    ID   BIGINT PRIMARY KEY
                      NOT NULL,
    Data TEXT
);
";
            SQLiteCommand cmdDB = new SQLiteCommand(sqlDB, Connection);
            cmdDB.CommandType = CommandType.Text;
            cmdDB.CommandTimeout = 60;//单位为秒
            cmdDB.ExecuteNonQuery();
            cmdDB.Dispose();
        }
        
        #endregion

        #region 日志写入数据库

        /// <summary>
        /// 日志写入数据库
        /// </summary>
        /// <param name="logData"></param>
        public void Write(LogContent logData)
        {
            
            SQLiteTransaction transaction = Connection.BeginTransaction();
            try
            {
                long logId = Snowflake.SnowflakeWorker.CreateId();

                string Sql = @"
INSERT INTO Logs (ID,Time,Level,Type,Message,StackTrace)
VALUES(@ID,@Time,@Level,@Type,@Message,@StackTrace);
";
                SQLiteCommand cmdLog = new SQLiteCommand(Sql, Connection);
                cmdLog.Transaction = transaction;
                cmdLog.CommandType = CommandType.Text;
                cmdLog.CommandTimeout = 60;//单位为秒
                cmdLog.Parameters.AddRange(new SQLiteParameter[] {
                    new SQLiteParameter("@ID", logId),
                    new SQLiteParameter("@Time", logData.Time),
                    new SQLiteParameter("@Type", string.IsNullOrEmpty(logData.Type) ? (object)DBNull.Value : logData.Type),
                    new SQLiteParameter("@Level", string.IsNullOrEmpty(logData.Level) ? (object)DBNull.Value : logData.Level),
                    new SQLiteParameter("@Message", string.IsNullOrEmpty(logData.Message) ? (object)DBNull.Value : logData.Message),
                    new SQLiteParameter("@StackTrace", string.IsNullOrEmpty(logData.StackTrace) ? (object)DBNull.Value : logData.StackTrace)
                });

                cmdLog.ExecuteNonQuery();
                cmdLog.Dispose();



                if (!string.IsNullOrEmpty(logData.Data))
                {
                    Sql = @" INSERT INTO LogsData (ID,Data) VALUES (@ID,@Data); ";
                    SQLiteCommand cmdLogData = new SQLiteCommand(Sql, Connection);
                    cmdLogData.Transaction = transaction;
                    cmdLogData.CommandType = CommandType.Text;
                    cmdLogData.CommandTimeout = 60;//单位为秒
                    cmdLogData.Parameters.AddRange(new SQLiteParameter[] {
                        new SQLiteParameter("@ID", logId),
                        new SQLiteParameter("@Data", logData.Data)
                    });

                    cmdLogData.ExecuteNonQuery();
                    cmdLogData.Dispose();
                }

                transaction.Commit();
            }
            catch (Exception exp)
            {
                transaction.Rollback();
                throw exp;
            }
            finally
            {
                transaction.Dispose();
            }

        }

        /// <summary>
        /// 日志写入数据库
        /// </summary>
        /// <param name="parDatas"></param>
        public void Write(List<LogContent> parDatas)
        {

            SQLiteTransaction transaction = Connection.BeginTransaction();
            try
            {

                foreach (LogContent logData in parDatas)
                {
                    string logId = //Md5(Guid.NewGuid().ToString("N"), true);
                    Snowflake.SnowflakeWorker.CreateStringId();


                    string Sql = @"
INSERT INTO Logs (ID,Time,Level,Type,Message,StackTrace)
VALUES(@ID,@Time,@Level,@Type,@Message,@StackTrace);
";
                    SQLiteCommand cmdLog = new SQLiteCommand(Sql, Connection);
                    cmdLog.Transaction = transaction;
                    cmdLog.CommandType = CommandType.Text;
                    cmdLog.CommandTimeout = 60;//单位为秒
                    cmdLog.Parameters.AddRange(new SQLiteParameter[] {
                        new SQLiteParameter("@ID", logId),
                        new SQLiteParameter("@Time", logData.Time),
                        new SQLiteParameter("@Type", string.IsNullOrEmpty(logData.Type) ? (object)DBNull.Value : logData.Type),
                        new SQLiteParameter("@Level", string.IsNullOrEmpty(logData.Level) ? (object)DBNull.Value : logData.Level),
                        new SQLiteParameter("@Message", string.IsNullOrEmpty(logData.Message) ? (object)DBNull.Value : logData.Message),
                        new SQLiteParameter("@StackTrace", string.IsNullOrEmpty(logData.StackTrace) ? (object)DBNull.Value : logData.StackTrace)
                    });

                    cmdLog.ExecuteNonQuery();
                    cmdLog.Dispose();



                    if (!string.IsNullOrEmpty(logData.Data))
                    {
                        Sql = @" INSERT INTO LogsData (ID,Data) VALUES (@ID,@Data); ";
                        SQLiteCommand cmdLogData = new SQLiteCommand(Sql, Connection);
                        cmdLogData.Transaction = transaction;
                        cmdLogData.CommandType = CommandType.Text;
                        cmdLogData.CommandTimeout = 60;//单位为秒
                        cmdLogData.Parameters.AddRange(new SQLiteParameter[] {
                            new SQLiteParameter("@ID", logId),
                            new SQLiteParameter("@Data", logData.Data)
                        });

                        cmdLogData.ExecuteNonQuery();
                        cmdLogData.Dispose();
                    }

                }
                transaction.Commit();
            }
            catch (Exception exp)
            {
                transaction.Rollback();
                throw exp;
            }
            finally
            {
                transaction.Dispose();
            }

        }


        #endregion

        #region  Read

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataTable GetLogs(string bTime, string eTime, string Type, string Message,string StackTrace, string Data)
        {

            string sql = @"SELECT ID,Time,Level,Type,Message,StackTrace FROM Logs";

            List<string> whereList = new List<string>();
            List<SQLiteParameter> parList = new List<SQLiteParameter>();

            #region where par

            if (!string.IsNullOrEmpty(bTime))
            {
                whereList.Add(@" Time >= @bTime ");
                parList.Add(new SQLiteParameter("@bTime", bTime));
            }
            if (!string.IsNullOrEmpty(eTime))
            {
                whereList.Add(@" Time <= @eTime ");
                parList.Add(new SQLiteParameter("@eTime", eTime));
            }

            if (!string.IsNullOrEmpty(Type))
            {
                whereList.Add(@" Type like '%'||@Type||'%' ");
                parList.Add(new SQLiteParameter("@Type", Type));
            }

            if (!string.IsNullOrEmpty(Message))
            {
                whereList.Add(@" Message like '%'||@Message||'%' ");
                parList.Add(new SQLiteParameter("@Message", Message));
            }
            if (!string.IsNullOrEmpty(StackTrace))
            {
                whereList.Add(@" StackTrace like '%'||@StackTrace||'%' ");
                parList.Add(new SQLiteParameter("@StackTrace", StackTrace));
            }
            if (!string.IsNullOrEmpty(Data))
            {
                sql = @"
SELECT log.ID,log.Time,log.Level,log.Type,log.Message,log.StackTrace FROM Logs log 
left join LogsData logD on logD.ID=log.ID
";
                whereList.Add(@" logD.Data like '%'||@Data||'%' ");
                parList.Add(new SQLiteParameter("@Data", Data));
            }


            #endregion


            if (whereList.Count > 0)
            {
                sql += @" where " + string.Join(" and ", whereList);
            }

            using (SQLiteCommand cmd = new SQLiteCommand(sql, Connection))
            {
                System.Data.Common.DbTransaction transaction = cmd.Connection.BeginTransaction();
                try
                {
                    if (parList.Count > 0)
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(parList.ToArray());
                    }

                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                    DataTable data = new DataTable();
                    adapter.Fill(data);

                    transaction.Commit();

                    adapter.Dispose();

                    return data;
                }
                catch (Exception exp)
                {
                    transaction.Rollback();
                    throw exp;
                }
                finally
                {
                    transaction.Dispose();
                }

            }

        }

        /// <summary>
        /// 分页查询日志列表
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageNumber"></param>
        /// <param name="sortField"></param>
        /// <param name="sortType"></param>
        /// <param name="bTime"></param>
        /// <param name="eTime"></param>
        /// <param name="Type"></param>
        /// <param name="Message"></param>
        /// <param name="StackTrace"></param>
        /// <param name="Data"></param>
        /// <returns></returns>
        public PagingLogData GetLogsByPage(int PageSize, int PageNumber, string sortField, string sortType
            , string bTime, string eTime, string Type, string Message, string StackTrace, string Data)
        {
            PagingLogData pData = new PagingLogData();

            if (PageNumber < 1)
            {
                PageNumber = 1;
            }
            if (PageSize < 1)
            {
                PageSize = 1;
            }

            pData.pagenumber = PageNumber;
            pData.pagesize = PageSize;


            List<string> whereList = new List<string>();
            List<SQLiteParameter> parList = new List<SQLiteParameter>();

            #region where par

            if (!string.IsNullOrEmpty(bTime))
            {
                whereList.Add(@" Time >= @bTime ");
                parList.Add(new SQLiteParameter("@bTime", bTime));
            }
            if (!string.IsNullOrEmpty(eTime))
            {
                whereList.Add(@" Time <= @eTime ");
                parList.Add(new SQLiteParameter("@eTime", eTime));
            }

            if (!string.IsNullOrEmpty(Type))
            {
                whereList.Add(@" Type like '%'||@Type||'%' ");
                parList.Add(new SQLiteParameter("@Type", Type));
            }

            if (!string.IsNullOrEmpty(Message))
            {
                whereList.Add(@" Message like '%'||@Message||'%' ");
                parList.Add(new SQLiteParameter("@Message", Message));
            }
            if (!string.IsNullOrEmpty(StackTrace))
            {
                whereList.Add(@" StackTrace like '%'||@StackTrace||'%' ");
                parList.Add(new SQLiteParameter("@StackTrace", StackTrace));
            }
            


            #endregion

            
            using (System.Data.Common.DbTransaction transaction = Connection.BeginTransaction()) {

                try
                {
                    //获取总条数SQL
                    string _countSQL = "SELECT count(ID) num FROM Logs";

                    if (!string.IsNullOrEmpty(Data))
                    {
                        _countSQL = @"
SELECT log.ID,log.Time,log.Level,log.Type,log.Message,log.StackTrace FROM Logs log 
left join LogsData logD on logD.ID=log.ID
";
                        whereList.Add(@" logD.Data like '%'||@Data||'%' ");
                        parList.Add(new SQLiteParameter("@Data", Data));
                    }

                    if (whereList.Count > 0)
                    {
                        _countSQL += @" where " + string.Join(" and ", whereList);
                    }
                    //获取总条数
                    using (SQLiteCommand cmdCount = new SQLiteCommand(_countSQL, Connection))
                    {
                        if (parList.Count > 0)
                        {
                            cmdCount.Parameters.Clear();
                            cmdCount.Parameters.AddRange(parList.ToArray());
                        }

                        pData.total = Convert.ToInt32(cmdCount.ExecuteScalar());
                    }

                    string sqlList = @"SELECT ID,Time,Level,Type,Message,StackTrace FROM Logs";
                    if (!string.IsNullOrEmpty(Data))
                    {
                        sqlList = @"
SELECT log.ID,log.Time,log.Level,log.Type,log.Message,log.StackTrace FROM Logs log 
left join LogsData logD on logD.ID=log.ID
";
                        whereList.Add(@" logD.Data like '%'||@Data||'%' ");
                        parList.Add(new SQLiteParameter("@Data", Data));
                    }
                    if (whereList.Count > 0)
                    {
                        sqlList += @" where " + string.Join(" and ", whereList);
                    }

                    if (!string.IsNullOrEmpty(sortField)) {
                        sqlList += " order by "+sortField + (sortType == "desc" ? " desc " : " ");
                    }
                    
                    //分页
                    sqlList += string.Format(" limit {0} offset {1} "
                        , PageSize
                        , PageSize * (PageNumber - 1)
                        );


                    //获取列表
                    using (SQLiteCommand cmdList = new SQLiteCommand(sqlList, Connection))
                    {
                        if (parList.Count > 0)
                        {
                            cmdList.Parameters.Clear();
                            cmdList.Parameters.AddRange(parList.ToArray());
                        }

                        using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmdList)) {
                            pData.rows = new DataTable();
                            adapter.Fill(pData.rows);
                        }
                    }

                    transaction.Commit();
                }
                catch (Exception exp)
                {
                    transaction.Rollback();
                    throw exp;
                }
            }

            return pData;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public object GetLogData(string ID)
        {
            string sql = @"SELECT Data FROM LogsData where ID=@ID";

            using (SQLiteCommand cmd = new SQLiteCommand(sql, Connection))
            {
                System.Data.Common.DbTransaction transaction = cmd.Connection.BeginTransaction();
                try
                {
                    cmd.Parameters.Add(new SQLiteParameter("@ID", ID));

                   object resObj = cmd.ExecuteScalar();
                    transaction.Commit();
                    return resObj;
                }
                catch (Exception exp)
                {
                    transaction.Rollback();
                    throw exp;
                }
                finally
                {
                    transaction.Dispose();
                }

            }

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public DataTable GetLogData2(string ID)
        {
            
            string sql = @"SELECT ID,Time,Level,Type,Message,Data FROM Logs where ID=@ID";

            using (SQLiteCommand cmd = new SQLiteCommand(sql, Connection))
            {
                System.Data.Common.DbTransaction transaction = cmd.Connection.BeginTransaction();
                try
                {
                    cmd.Parameters.Add(new SQLiteParameter("@ID", ID));
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                    DataTable data = new DataTable();
                    adapter.Fill(data);
                    transaction.Commit();

                    adapter.Dispose();


                    return data;
                }
                catch (Exception exp)
                {
                    transaction.Rollback();
                    throw exp;
                }
                finally
                {
                    transaction.Dispose();
                }

            }

        }

        #endregion

        #region Dispose

        private bool IsDispose = false;
        public void Dispose()
        {
            if (!IsDispose) {
                IsDispose = true;

                if (_conn != null)
                {
                    _conn.Dispose();
                }

            }
        }
        #endregion

    }

}